# -*- coding: utf-8 -*-
# @Time    : 2023/10/12 15:00
# @Author  : yan.wei
# @Email   : 13675196684@163.com
# @File    : main4.py
# @Software: PyCharm

import pandas as pd

# 方法一
# 创建dim_ci表
#获取列名和数据
df1 = pd.read_csv("Engineering_Parameters\dim_ci.csv")

# 获取列名，通过dtype方法查询出数据的列名和列的数据类型
df1.dtypes.to_csv("dim_ci_dtypes.txt", index=True, header="False")

# 读取文本文件
df = pd.read_csv('dim_ci_dtypes.txt', header=None)

# 在每一行的末尾添加逗号
df = df.apply(lambda x: x.astype(str) + ',')

# 保存为txt
df.to_csv('dim_ci_column.txt', index=False, header=False, quotechar=' ')

# 数据类型映射关系
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
    if "object" in str(j):
            dtypedict.update({i: 'string'})
    elif "float" in str(j):
            dtypedict.update({i: 'double'}) # hive中这两者都兼容， FLOAT： 单精度浮点数，占用4个字节，可以存储大约7位有效数字。DOUBLE： 双精度浮点数，占用8个字节，可以存储大约15位有效数字。
    elif "int" in str(j):
        dtypedict.update({i: 'bigint'}) # hive支持int/integer, smallint, tinyint, bigint
    elif "datetime" in str(j):
        dtypedict.update({i: 'datetime'})
    else:
        dtypedict.update({i: 'string'})

# 修改dim_ci_column.txt中列的数据类型
"""
CREATE EXTERNAL TABLE dim_ci (
    province int, 
    provincename string, 
    city int, 
    cityname string, 
    area int, 
    areaname string, 
    town float, 
    country float, 
    rat int, 
    mcc int, 
    mnc int, 
    tac int, 
    lac float, 
    siteid int, 
    site_name string, 
    cellid int, 
    ci int, 
    ciname string, 
    cigroup float, 
    ciaddr float, 
    hotcell float, 
    ecgi int, 
    vendor_id float, 
    vendor string, 
    cover float, 
    covertype string, 
    freqband string, 
    longitude float, 
    latitude float, 
    height float, 
    azimuth float, 
    etilt float, 
    mtilt float, 
    pci int, 
    earfcn float, 
    hbwd int, 
    vbwd int, 
    areatype int, 
    areatype_name string, 
    coverscene float, 
    coverscenename string, 
    siteid_e2e int, 
    dlband float, 
    ulband float, 
    siteid_len float
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
tblproperties("skip.header.line.count"="1");
"""

# 加载数据
# LOAD DATA INPATH '/case/iraho/dim_ci.csv' INTO TABLE dim_ci;


# 创建fact_epsfb_bigxdr_20220614表
# 同样的方法

"""

CREATE EXTERNAL TABLE fact_epsfb_bigxdr_20220615 (
epsfb_procedurestarttime TIMESTAMP,
epsfb_procedureendtime TIMESTAMP,
  xdrid INTEGER,
  xdr_merge_status INTEGER,
  interface_merge_status INTEGER,
  epsfb_type INTEGER,
  epsfb_userstatus INTEGER,
  n2_pdusessionresourcemodify_procedurestarttime TIMESTAMP,
  n2_pdusessionresourcemodify_delay INTEGER,
  n2_pdusessionresourcemodify_procedurestatus INTEGER,
  n2_pdusessionresourcemodify_causetype INTEGER,
  n2_pdusessionresourcemodify_cause INTEGER,
  n2_pdusessionresourcemodify_amfip STRING,
  n2_pdusessionresourcemodify_gnbip STRING,
  n2_pdusessionresourcemodify_tac INTEGER,
  n2_pdusessionresourcemodify_eci INTEGER,
  n6_procedurestarttime TIMESTAMP,
  n6_procedureendtime TIMESTAMP,
  n6_183time INTEGER,
  n6_180time INTEGER,
  n6_release_time INTEGER,
  n6_procedurestatus INTEGER,
  n6_causetype INTEGER,
  n6_cause INTEGER,
  n6_invite_times INTEGER,
  call_number1 FLOAT,
  call_imsi1 FLOAT,
  call_imei1 FLOAT,
  call_localprovince1 INTEGER,
  call_localcity1 INTEGER,
  call_localarea1 INTEGER,
  call_ownerprovince1 INTEGER,
  call_npoperator1 INTEGER,
  call_number2 FLOAT,
  call_imsi2 FLOAT,
  call_imei2 FLOAT,
  call_localprovince2 INTEGER,
  call_localcity2 INTEGER,
  call_npoperator2 INTEGER,
  n2_paging_procedurestarttime TIMESTAMP,
  n2_paging_count INTEGER,
  n2_paging_servicerequest_delay INTEGER,
  n2_paging_noresponsecount INTEGER,
  n2_servicerequest_procedurestarttime TIMESTAMP,
  rx_aar_procedurestarttime TIMESTAMP,
  rx_aar_delay INTEGER,
  rx_aar_procedurestatus INTEGER,
  rx_aar_causetype INTEGER,
  rx_aar_cause INTEGER,
  rx_aar_sbcip STRING,
  rx_aar_pcrfip STRING,
  n5_policyauthorizationnotify_procedurestarttime FLOAT,
  n5_policyauthorizationnotify_delay FLOAT,
  n5_policyauthorizationnotify_procedurestatus FLOAT,
  n5_policyauthorizationnotify_causetype FLOAT,
  n5_policyauthorizationnotify_cause FLOAT,
  n5_policyauthorizationnotify_sbcip FLOAT,
  n5_policyauthorizationnotify_pcrfip FLOAT,
  n7_updatenotify_procedurestarttime TIMESTAMP,
  n7_updatenotify_delay INTEGER,
  n7_updatenotify_procedurestatus INTEGER,
  n7_updatenotify_causetype INTEGER,
  n7_updatenotify_cause INTEGER,
  n7_updatenotify_smfip STRING,
  n7_updatenotify_pcfip STRING,
  n11_n1n2messegetransfer_procedurestarttime TIMESTAMP,
  n11_n1n2messegetransfer_delay INTEGER,
  n11_n1n2messegetransfer_procedurestatus INTEGER,
  n11_n1n2messegetransfer_causetype INTEGER,
  n11_n1n2messegetransfer_cause INTEGER,
  n11_n1n2messegetransfer_amfip FLOAT,
  n11_n1n2messegetransfer_smfip FLOAT,
  n2_handoverout_procedurestarttime TIMESTAMP,
  n2_handoverout_delay INTEGER,
  n2_handoverout_procedurestatus INTEGER,
  n2_handoverout_causetype INTEGER,
  n2_handoverout_cause INTEGER,
  n2_handoverout_amfip STRING,
  n2_handoverout_gnbip STRING,
  n2_handoverout_tac INTEGER,
  n2_handoverout_eci INTEGER,
  n2_handovercancel_procedurestarttime TIMESTAMP,
  n2_handovercancel_causetype INTEGER,
  n2_handovercancel_cause INTEGER,
  n26_forwardrelocation_procedurestarttime TIMESTAMP,
  n26_forwardrelocation_delay INTEGER,
  n26_forwardrelocation_procedurestatus INTEGER,
  n26_forwardrelocation_causetype INTEGER,
  n26_forwardrelocation_cause INTEGER,
  n26_forwardrelocation_amfip STRING,
  n26_forwardrelocation_mmeip STRING,
  n26_relocationcancel_procedurestarttime TIMESTAMP,
  n26_relocationcancel_causetype INTEGER,
  n26_relocationcancel_cause INTEGER,
  s1mme_handoverin_procedurestarttime TIMESTAMP,
  s1mme_handoverin_delay INTEGER,
  s1mme_handoverin_procedurestatus INTEGER,
  s1mme_handoverin_causetype INTEGER,
  s1mme_handoverin_cause INTEGER,
  s1mme_handoverin_mmeip STRING,
  s1mme_handoverin_enbip STRING,
  s1mme_handoverin_tac INTEGER,
  s1mme_handoverin_eci INTEGER,
  n11_updatesmcontext_procedurestarttime TIMESTAMP,
  n11_updatesmcontext_delay INTEGER,
  n11_updatesmcontext_procedurestatus INTEGER,
  n11_updatesmcontext_causetype INTEGER,
  n11_updatesmcontext_cause INTEGER,
  n11_updatesmcontext_amfip FLOAT,
  n11_updatesmcontext_smfip FLOAT,
  n26_forwardrelocationcompletenotification_procedurestarttime TIMESTAMP,
  n26_forwardrelocationcompletenotification_delay INTEGER,
  n26_forwardrelocationcompletenotification_procedurestatus INTEGER,
  n26_forwardrelocationcompletenotification_causetype INTEGER,
  n26_forwardrelocationcompletenotification_cause INTEGER,
  n26_handoverprepare_complete_delay INTEGER,
  n26_handovercomplete_delay INTEGER,
  s1mme_tau_procedurestarttime TIMESTAMP,
  s1mme_tau_delay INTEGER,
  s1mme_tau_procedurestatus INTEGER,
  s1mme_tau_causetype INTEGER,
  s1mme_tau_cause INTEGER,
  s1mme_tau_mmeip STRING,
  s1mme_tau_enbip STRING,
  s1mme_tau_tac INTEGER,
  s1mme_tau_eci INTEGER,
  s1mme_attach_procedurestarttime TIMESTAMP,
  s1mme_attach_delay INTEGER,
  s1mme_attach_procedurestatus INTEGER,
  s1mme_attach_causetype INTEGER,
  s1mme_attach_cause INTEGER,
  s1mme_attach_mmeip FLOAT,
  s1mme_attach_enbip FLOAT,
  s1mme_attach_tac INTEGER,
  s1mme_attach_eci INTEGER,
  n26_contextrequest_fallback_procedurestarttime TIMESTAMP,
  n26_contextrequest_fallback_delay INTEGER,
  n26_contextrequest_fallback_procedurestatus INTEGER,
  n26_contextrequest_fallback_causetype INTEGER,
  n26_contextrequest_fallback_cause INTEGER,
  n26_contextrequest_fallback_amfip STRING,
  n26_contextrequest_fallback_mmeip STRING,
  s11_createsession_procedurestarttime FLOAT,
  s11_createsession_delay FLOAT,
  s11_createsession_procedurestatus FLOAT,
  s11_createsession_causetype FLOAT,
  s11_createsession_cause FLOAT,
  s11_createsession_mmeip FLOAT,
  s11_createsession_sgwip FLOAT,
  n2_uecontextrelease_procedurestarttime TIMESTAMP,
  n2_uecontextrelease_direction INTEGER,
  n2_uecontextrelease_causetype INTEGER,
  n2_uecontextrelease_cause INTEGER,
  s1mme_extendedservicerequest_procedurestarttime TIMESTAMP,
  s1mme_extendedservicerequest_delay INTEGER,
  s1mme_extendedservicerequest_procedurestatus INTEGER,
  s1mme_extendedservicerequest_causetype INTEGER,
  s1mme_extendedservicerequest_cause INTEGER,
  s1mme_extendedservicerequest_mmeip STRING,
  s1mme_extendedservicerequest_enbip STRING,
  s1mme_extendedservicerequest_tac INTEGER,
  s1mme_extendedservicerequest_eci INTEGER,
  csfb_delay INTEGER,
  s11_createbearer_procedurestarttime FLOAT,
  s11_createbearer_delay FLOAT,
  s11_createbearer_procedurestatus FLOAT,
  s11_createbearer_causetype FLOAT,
  s11_createbearer_cause FLOAT,
  s11_createbearer_mmeip FLOAT,
  s11_createbearer_sgwip FLOAT,
  s1mme_bearersetup_procedurestarttime TIMESTAMP,
  s1mme_bearersetup_delay INTEGER,
  s1mme_bearersetup_procedurestatus INTEGER,
  s1mme_bearersetup_causetype INTEGER,
  s1mme_bearersetup_cause INTEGER,
  s1mme_bearersetup_mmeip STRING,
  s1mme_bearersetup_enbip STRING,
  gx_ccr_procedurestarttime TIMESTAMP,
  gx_ccr_delay INTEGER,
  gx_ccr_procedurestatus INTEGER,
  gx_ccr_causetype INTEGER,
  gx_ccr_cause INTEGER,
  gx_ccr_pgwip FLOAT,
  gx_ccr_pcrfip FLOAT,
  n7_update_procedurestarttime TIMESTAMP,
  n7_update_delay INTEGER,
  n7_update_procedurestatus INTEGER,
  n7_update_causetype INTEGER,
  n7_update_cause INTEGER,
  n7_update_smfip STRING,
  n7_update_pcfip STRING,
  rx_rar_procedurestarttime TIMESTAMP,
  rx_rar_delay INTEGER,
  rx_rar_procedurestatus INTEGER,
  rx_rar_causetype INTEGER,
  rx_rar_cause INTEGER,
  rx_rar_pcrfip STRING,
  rx_rar_sbcip STRING,
  n5_policyauthorizationupdate_procedurestarttime FLOAT,
  n5_policyauthorizationupdate_delay FLOAT,
  n5_policyauthorizationupdate_procedurestatus FLOAT,
  n5_policyauthorizationupdate_causetype FLOAT,
  n5_policyauthorizationupdate_cause FLOAT,
  n5_policyauthorizationupdate_pcrfip FLOAT,
  n5_policyauthorizationupdate_sbcip FLOAT,
  sbcmw_procedurestarttime TIMESTAMP,
  sbcmw_procedureendtime TIMESTAMP,
  sbcmw_183_time INTEGER,
  sbcmw_alerting_time INTEGER,
  sbcmw_answer_time INTEGER,
  sbcmw_release_time INTEGER,
  sbcmw_call_duration INTEGER,
  sbcmw_firfailtime INTEGER,
  sbcmw_procedurestatus INTEGER,
  sbcmw_causetype INTEGER,
  sbcmw_cause INTEGER,
  sbcmw_z_reason_text STRING,
  sbcmw_z_reason_protocol INTEGER,
  sbcmw_z_reason_cause INTEGER,
  sbcmw_z_x_zte_cause STRING,
  sbcmw_sbcip STRING,
  sbcmw_scscfip STRING,
  sbcmw_net_invite INTEGER,
  icid STRING,
  astriggerlist INTEGER,
  call_scene INTEGER,
  calling_radio_network INTEGER,
  called_radio_network INTEGER,
  called_csretry INTEGER,
  sipfirstfail_z_reason_text STRING,
  sipfirstfail_z_reason_protocol INTEGER,
  sipfirstfail_z_reason_cause INTEGER,
  sipfirstfail_z_x_zte_cause STRING,
  sipfirstfail_causetype INTEGER,
  sipfirstfail_cause INTEGER,
  sipfirstfail_message INTEGER,
  sipfirstfail_netip STRING,
  sipfirstfail_netype INTEGER,
  sipfirstfail_call_side INTEGER,
  sipfirstfail_protocol INTEGER,
  sipfirstfail_interface INTEGER,
  callfirstfail_interface INTEGER,
  callfirstfail_message INTEGER,
  callfirstfail_protocol INTEGER,
  callfirstfail_causetype INTEGER,
  callfirstfail_cause INTEGER,
  callfirstfail_netip STRING,
  callfirstfail_netype INTEGER,
  callfirstfail_call_side INTEGER,
  delimit_180 INTEGER,
  subdelimit_180 INTEGER,
  byetac_4g INTEGER,
  byeeci_4g INTEGER,
  n2_registration_procedurestarttime TIMESTAMP,
  n2_registration_delay INTEGER,
  n2_registration_procedurestatus INTEGER,
  n2_registration_causetype INTEGER,
  n2_registration_cause INTEGER,
  n2_registration_amfip STRING,
  n2_registration_gnbip STRING,
  n2_registration_subtype INTEGER,
  n2_registration_tac INTEGER,
  n2_registration_eci INTEGER,
  n26_contextrequest_procedurestarttime TIMESTAMP,
  n26_contextrequest_delay INTEGER,
  n26_contextrequest_procedurestatus INTEGER,
  n26_contextrequest_causetype INTEGER,
  n26_contextrequest_cause INTEGER,
  n26_contextrequest_amfip STRING,
  n26_contextrequest_mmeip STRING,
  data_quality INTEGER,
  delay_indication_epsfb INTEGER,
  epsfb_mode INTEGER,
  pdusessionresourcemodify_handoverout_delay INTEGER,
  handoverout_tauorattach_delay INTEGER,
  pdusessionresourcemodify_uerelease_delay INTEGER,
  uerelease_tauorattach_delay INTEGER,
  tac_4g INTEGER,
  eci_4g INTEGER,
  have_epsfb_try INTEGER,
  have_epsfb INTEGER,
  have_epsfb_success INTEGER,
  epsfbfirstfail_causetype INTEGER,
  epsfbfirstfail_cause INTEGER,
  epsfbfirstfail_interface INTEGER,
  epsfbfirstfail_proceduretype INTEGER,
  epsfbfirstfail_netip STRING,
  epsfbfirstfail_netype INTEGER,
  epsfb_delimit INTEGER,
  epsfb_subdelimit INTEGER,
  epsfb_delay INTEGER,
  epsfb_success_delay INTEGER,
  epsfb_delay_n26 INTEGER,
  have_epsfb_continue_mw INTEGER,
  have_epsfb_180or200invite_mw INTEGER,
  have_epsfb_180or200invite_net_mw INTEGER,
  epsfb_180_delay_mw INTEGER,
  epsfb_callend_time_mw STRING,
  rx_asr_causetype INTEGER,
  rx_asr_cause INTEGER,
  have_epsfb_drop INTEGER,
  epsfb_result INTEGER,
  have_epsfb_return INTEGER,
  have_epsfb_return_success INTEGER,
  epsfb_return_delay INTEGER,
  csfb_reason INTEGER,
  keyword1 INTEGER,
  vendor INTEGER,
  model INTEGER,
  s1mme_tau_xdrid STRING,
  n2_handoverout_othertac INTEGER,
  n2_handoverout_othereci INTEGER,
  rx_asr_tac INTEGER,
  rx_asr_eci INTEGER,
  n6_firfailtime INTEGER,
  n6_sbcip STRING,
  gminvite_aar_delay INTEGER,
  gminvite_paging_delay INTEGER,
  servicerequest_gm183_delay INTEGER,
  gm183_aar_delay INTEGER,
  aar_psrm_delay INTEGER,
  handoutreq_handinreq_delay INTEGER,
  handinreq_handoutcommand_delay INTEGER,
  handoutcommand_handinnotify_delay INTEGER,
  handinnotify_tau_delay INTEGER,
  bearersetupcomplete_invite_delay INTEGER,
  n2_pdusessionresourcemodify_count INTEGER,
  n6_answer_time INTEGER,
  n6_call_duration INTEGER,
  n6_z_reason_text STRING,
  n6_z_reason_protocol INTEGER,
  n6_z_reason_cause INTEGER,
  sip_calling_result INTEGER,
  n6_net_invite INTEGER,
  call_epsfb2 INTEGER,
  epsfb_procedureendtime_correct TIMESTAMP,
  s1mme_tau2_procedurestarttime TIMESTAMP,
  s1mme_tau2_delay INTEGER,
  s1mme_tau2_procedurestatus INTEGER,
  s1mme_tau2_causetype INTEGER,
  s1mme_tau2_cause INTEGER,
  s1mme_erabsetup_delay INTEGER,
  s1mme_erabsetup_procedurestatus INTEGER,
  s1mme_erabsetup_causetype INTEGER,
  s1mme_erabsetup_cause INTEGER,
  n6_net_invite_sx INTEGER,
  sbcmw_net_invite_sx INTEGER,
  have_epsfb_180or200invite_net_sx INTEGER,
  sbcmw_183_prack_delay INTEGER,
  sbcmw_prack_200prack_delay INTEGER,
  sbcmw_200prack_update_delay INTEGER,
  sbcmw_update_200update_delay INTEGER,
  sbcmw_200update_180_delay INTEGER,
  sbcmw_180_200invite_delay INTEGER,
  astriggerlist2 INTEGER,
  astriggernumber INTEGER,
  epsfb_return_success_delay INTEGER
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
tblproperties("skip.header.line.count"="1") ;
"""

# 加载数据
"""
LOAD DATA INPATH '/case/iraho/fact_epsfb_bigxdr_20220616.csv' INTO TABLE fact_epsfb_bigxdr_20220616;
LOAD DATA INPATH '/case/iraho/fact_epsfb_bigxdr_20220617.csv' INTO TABLE fact_epsfb_bigxdr_20220617;
LOAD DATA INPATH '/case/iraho/fact_epsfb_bigxdr_20220618.csv' INTO TABLE fact_epsfb_bigxdr_20220618;
LOAD DATA INPATH '/case/iraho/fact_epsfb_bigxdr_20220619.csv' INTO TABLE fact_epsfb_bigxdr_20220619;
LOAD DATA INPATH '/case/iraho/fact_epsfb_bigxdr_20220620.csv' INTO TABLE fact_epsfb_bigxdr_20220620;
"""

# 方法二： 通过pandas的pd.to_sql方法,将数据先一键导入mysql、sqlite
# 通过show create table fact_epsfb_bigxdr_20220616; 查看创表语法，修改列的数据类型

# 方法三： 通过pandas的pd.to_sql方法,将数据先一键导入mysql、sqlite
# 使用sqoop的import-all-tables命令，将mysql中的所有表导入hive